package com.css.fxfzypg.modules.baseData.repository.impl;

import com.css.fxfzypg.constants.YNEnum;
import com.css.fxfzypg.modules.baseData.repository.ElevationRepository;
import com.css.fxfzypg.modules.baseData.entity.Elevation;
import com.css.fxfzypg.modules.baseData.queryParams.ElevationQueryParams;
import com.css.fxfzypg.modules.baseData.vo.ElevationVO;
import com.css.fxfzypg.util.PlatformObjectUtils;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class ElevationRepositoryImpl implements ElevationRepository {
    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    @Transactional(rollbackFor = Exception.class)
    public ElevationVO updateElevation(ElevationVO elevationVO) {

        String sql = "update sys_elevation set elevation_number = :elevationNumber, elevation_band = :elevationBand, elevation_nodatavalue = :elevationNodatavalue, elevation_min = :elevationMin, elevation_max = :elevationMax  where elevation_id = '" + elevationVO.getElevationId() + "'";

        SqlParameterSource source = new BeanPropertySqlParameterSource(elevationVO);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return elevationVO;
    }

    @Override
    @Transactional
    public Elevation saveElevation(Elevation elevation) {
        String sql = "insert into sys_elevation values(:elevationId,:elevationNumber,:elevationBand,:elevationNodatavalue,:elevationMin,:elevationMax,:elevationStatus)";

        SqlParameterSource source = new BeanPropertySqlParameterSource(elevation);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return elevation;
    }

    @Override
    public Page<Elevation> getAllElevationParams(ElevationQueryParams queryParams) {
        StringBuilder sql = new StringBuilder(" select  e.* from sys_elevation e ");

        StringBuilder orderSql = new StringBuilder(" order by e.elevation_number");

        StringBuilder whereSql = new StringBuilder(" where e.elevation_status = '" + YNEnum.Y.toString() + "'");

        /*if (!PlatformObjectUtils.isEmpty(queryParams.getElevationBand()) ){
            whereSql.append(" and e.elevation_band like '%" + queryParams.getElevationBand() + "%'");
        }

        if (!PlatformObjectUtils.isEmpty(queryParams.getElevationNodatavalue()) ){
           whereSql.append(" and e.elevation_nodatavalue like '%" + queryParams.getElevationNodatavalue() + "%'");
        }*/

        String wholeSql = sql.append(whereSql).append(orderSql).toString();
        List<Elevation> list = jdbcTemplate.query(wholeSql, new BeanPropertyRowMapper<>(Elevation.class));
        Pageable pageable = PageRequest.of(queryParams.getCurPage() - 1, queryParams.getPageSize());

        Page<Elevation> elevations = new PageImpl<>(list, pageable, PlatformObjectUtils.isEmpty(list) ? 0 : list.size());

        return elevations;
    }

    @Override
    public Elevation getElevationById(String id) {
        String sql = "select * from sys_elevation where elevation_id = '" + id + "'";
        Elevation elevation = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Elevation.class));
        return elevation;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void deleteElevationsById(List<String> ids, Timestamp timestamp) {
        String sql = "update sys_elevation set elevation_status = '" + YNEnum.N + "' , elevation_updatetime = '" + timestamp + "' " + " where elevation_id in (:ids)";
        Map map = new HashMap();
        map.put("ids", ids);
        namedParameterJdbcTemplate.update(sql, map);
    }

    @Override
    public List<Elevation> getAll() {
        return jdbcTemplate.query("select * from sys_elevation", new BeanPropertyRowMapper<>(Elevation.class));
    }

    @Override
    public void importExcel(List<Elevation> elevations) {
        jdbcTemplate.batchUpdate("insert into sys_elevation (elevation_id,elevation_number,elevation_band,elevation_nodatavalue,elevation_min,elevation_max,elevation_status)values(?,?,?,?,?,?,?)", new BatchPreparedStatementSetter() {
            @Override
            public int getBatchSize() {
                return elevations.size();
            }

            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1, elevations.get(i).getElevationId());
                ps.setInt(2, elevations.get(i).getElevationNumber());
                /*ps.setString(3,elevations.get(i).getElevationBand());
                ps.setString(4,elevations.get(i).getElevationNodatavalue());
                ps.setInt(5,elevations.get(i).getElevationMin());
                ps.setInt(6,elevations.get(i).getElevationMax());
                ps.setString(7,elevations.get(i).getElevationStatus());*/
            }
        });
    }

    @Override
    public Integer countByCounty(String province, String city, String county) {
        return jdbcTemplate.queryForObject("SELECT count(*) from qh_map_files where  type ='地形地貌（高程）_' and   province = '" + province + "' and city = '" + city + "' and area = '" + county + "' and is_delete='0'  ", Integer.class);
    }

    @Override
    public Integer countByCity(String province, String city) {
        return jdbcTemplate.queryForObject("SELECT count(*) from qh_map_files where type ='地形地貌（高程）_' and  province = '" + province + "' and city = '" + city + "'  and is_delete='0' ", Integer.class);
    }

    @Override
    public Integer countByProvince(String province) {
        return jdbcTemplate.queryForObject("SELECT count(*) from qh_map_files where  type ='地形地貌（高程）_' and  province = '" + province + "' and is_delete='0'  ", Integer.class);
    }

    @Override
    public Integer countByProvinceAndYear(String provinceName, String taskYear) {
      //  return jdbcTemplate.queryForObject("SELECT count(t1.*) from sys_elevation_union t1 LEFT JOIN qh_map_files t2 on t1.fileuuid = t2.uuid where t1.province = ? and (t2.update_time LIKE ? or t2.create_time LIKE ?)", Integer.class, provinceName, "%" + taskYear + "%", "%" + taskYear + "%");
        return jdbcTemplate.queryForObject("SELECT count(*) from  qh_map_files  where type ='地形地貌（高程）_' and province = ? and (update_time LIKE ? or create_time LIKE ?) and is_delete='0' ", Integer.class, provinceName, "%" + taskYear + "%", "%" + taskYear + "%");
    }

}
